﻿using System.Threading.Tasks;
using System;
using Microsoft.EntityFrameworkCore;
using Oracle.ManagedDataAccess.Client;
using Microsoft.AspNetCore.Http;
using Simple.Services.System;
using Simple.Services.System.QHSEUser.Models;
using Simple.Services.System.QHSEViolationCategory.Models;
using Simple.Services.System.QHSEDanger.Models.AnalysisInput;

namespace Simple.Services;

public class DangerTypeAnalysisService
{
    private readonly SimpleDbContext _context;
    private readonly CacheService _cacheService;
    private readonly QHSEKeyWorkSpecialtyService _keyWorkSpecialtyService;
    private readonly QHSEDictionaryService _dictionaryService;
    private readonly QHSEDangerCategoryService _dangerCategoryService;
    public DangerTypeAnalysisService(SimpleDbContext context, QHSEKeyWorkSpecialtyService keyWorkSpecialtyService, QHSEDictionaryService dictionaryService, QHSEDangerCategoryService dangerCategoryService, CacheService cacheService)
    {
        _context = context;
        _keyWorkSpecialtyService = keyWorkSpecialtyService;
        _dictionaryService = dictionaryService;
        _dangerCategoryService = dangerCategoryService;
        _cacheService = cacheService;
    }
    private static string EscapeLikePattern(string pattern)
    {
        return pattern.Replace("'", "''").Replace("/", "//").Replace("_", "/_").Replace("%", "/%");
    }
    public async Task<List<DangerAnalysisResult>> DangerTypeAnalysisAsync(DangerAnalysisInput input)
    {
        var cachedResult = await _cacheService.GetDangerTypeDangerTypeAnalysisAsync(input);
        if (cachedResult != null)
        {
            return cachedResult;
        }

        List<DangerAnalysisResult> result = new List<DangerAnalysisResult>();
        var query = _context.Set<QHSESysDanger>().AsNoTracking().AsQueryable();

        if (!string.IsNullOrEmpty(input.DangerOrganizationName) && input.TimeOn != null && input.TimeEnd != null && input.CheckType.Count != 0)
        {
            var org = await _context.Set<QHSESysOrganization>().FirstOrDefaultAsync(u => u.OrganizationId == input.DangerOrganizationId);
            if (org == null) return result;

            var escapedNamePath = EscapeLikePattern(org.NamePath);
            query = query.Where(u => input.CheckType.Contains(u.CheckType))
                         .Where(u => u.CheckDate >= input.TimeOn && u.CheckDate <= input.TimeEnd)
                         .Join(_context.Set<QHSESysOrganization>(),
                               danger => danger.OrganizationId,
                               organization => organization.OrganizationId,
                               (danger, organization) => new { Danger = danger, Organization = organization })
                         .Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"{escapedNamePath}%", "/"))
                         .Select(joinResult => joinResult.Danger);

            if (!string.IsNullOrEmpty(input.CheckOrganizationId))
            {
                var corg = await _context.Set<QHSESysOrganization>().FirstOrDefaultAsync(u => u.OrganizationId == input.CheckOrganizationId);
                if (corg != null)
                {
                    var escapedcNamePath = EscapeLikePattern(corg.NamePath);
                    query = query.Join(_context.Set<QHSESysOrganization>(),
                                       danger => danger.CheckOrganizationId,
                                       organization => organization.OrganizationId,
                                       (danger, organization) => new { Danger = danger, Organization = organization })
                                 .Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"%{escapedcNamePath}%", "/"))
                                 .Select(joinResult => joinResult.Danger);
                }
            }

            var dangerTypes = await _dictionaryService.GetDictionaryAsync("1010");
            var dangerCounts = await query
                .GroupBy(d => d.DangerType)
                .Select(g => new
                {
                    DangerType = g.Key,
                    Count = g.Count()
                })
                .ToListAsync();

            foreach (var type in dangerTypes)
            {
                var count = dangerCounts.FirstOrDefault(dt => dt.DangerType == type.Item)?.Count ?? 0;
                result.Add(new DangerAnalysisResult { Name = type.Item, Value = count });
            }
        }

        result = result.OrderByDescending(result => result.Value).ToList();
        await _cacheService.SetDangerTypeDangerTypeAnalysisAsync(input, result);
        return result;
    }

    public async Task<CompareDangerAnalysisResult> DangerTypeCategoryAnalysisAsync(DangerAnalysisInput input)
    {
        var cachedResult = await _cacheService.GetDangerTypeDangerTypeCategoryAnalysisAsync(input);
        if (cachedResult != null)
        {
            return cachedResult;
        }

        CompareDangerAnalysisResult result = new CompareDangerAnalysisResult();
        var query = _context.Set<QHSESysDanger>().AsNoTracking().AsQueryable();

        if (!string.IsNullOrEmpty(input.DangerOrganizationName) && input.TimeOn != null && input.TimeEnd != null && input.CheckType.Count != 0)
        {
            var org = await _context.Set<QHSESysOrganization>().FirstOrDefaultAsync(u => u.OrganizationId == input.DangerOrganizationId);
            if (org == null) return result;

            var escapedNamePath = EscapeLikePattern(org.NamePath);
            query = query.Where(u => input.CheckType.Contains(u.CheckType))
                         .Where(u => u.CheckDate >= input.TimeOn && u.CheckDate <= input.TimeEnd)
                         .Join(_context.Set<QHSESysOrganization>(),
                               danger => danger.OrganizationId,
                               organization => organization.OrganizationId,
                               (danger, organization) => new { Danger = danger, Organization = organization })
                         .Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"{escapedNamePath}%", "/"))
                         .Select(joinResult => joinResult.Danger);

            if (!string.IsNullOrEmpty(input.CheckOrganizationId))
            {
                var corg = await _context.Set<QHSESysOrganization>().FirstOrDefaultAsync(u => u.OrganizationId == input.CheckOrganizationId);
                if (corg != null)
                {
                    var escapedcNamePath = EscapeLikePattern(corg.NamePath);
                    query = query.Join(_context.Set<QHSESysOrganization>(),
                                       danger => danger.CheckOrganizationId,
                                       organization => organization.OrganizationId,
                                       (danger, organization) => new { Danger = danger, Organization = organization })
                                 .Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"%{escapedcNamePath}%", "/"))
                                 .Select(joinResult => joinResult.Danger);
                }
            }

            var dangerTypes = await _dictionaryService.GetDictionaryAsync("1010");
            var types = await _dictionaryService.GetDictionaryAsync("1013");
            var dangerCounts = await query
            .GroupBy(d => new { DangerType = d.DangerType ?? string.Empty, DangerTypeCategory = d.DangerTypeCategory ?? string.Empty })
            .Select(g => new
            {
                g.Key.DangerType,
                g.Key.DangerTypeCategory,
                Count = g.Count()
            })
            .ToListAsync();

            List<Object> vio = new List<Object> { "通用/专业" };
            vio.AddRange(types.Select(type => type.Item));
            result.Item.Add(vio);

            foreach (var dangerType in dangerTypes)
            {
                List<Object> violation = new List<Object> { dangerType.Item };
                foreach (var type in types)
                {
                    var count = dangerCounts.FirstOrDefault(dc => dc.DangerType == dangerType.Item && (dc.DangerTypeCategory == type.Item || dc.DangerTypeCategory == null))?.Count ?? 0;
                    violation.Add(count);
                }
                result.Item.Add(violation);
            }
        }

        await _cacheService.SetDangerTypeDangerTypeCategoryAnalysisAsync(input, result);
        return result;
    }

    public async Task<CompareDangerAnalysisResult> WorkSpecialityAnalysisAsync(DangerAnalysisInput input)
    {
        var cachedResult = await _cacheService.GetDangerTypeWorkSpecialityAnalysisAsync(input);
        if (cachedResult != null)
        {
            return cachedResult;
        }

        CompareDangerAnalysisResult result = new CompareDangerAnalysisResult();
        var query = _context.Set<QHSESysDanger>().AsNoTracking().AsQueryable();

        if (!string.IsNullOrEmpty(input.DangerOrganizationName) && input.TimeOn != null && input.TimeEnd != null && input.CheckType.Count != 0)
        {
            var org = await _context.Set<QHSESysOrganization>().FirstOrDefaultAsync(u => u.OrganizationId == input.DangerOrganizationId);
            if (org == null) return result;

            var escapedNamePath = EscapeLikePattern(org.NamePath);
            query = query.Where(u => input.CheckType.Contains(u.CheckType))
                         .Where(u => u.CheckDate >= input.TimeOn && u.CheckDate <= input.TimeEnd)
                         .Join(_context.Set<QHSESysOrganization>(),
                               danger => danger.OrganizationId,
                               organization => organization.OrganizationId,
                               (danger, organization) => new { Danger = danger, Organization = organization })
                         .Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"{escapedNamePath}%", "/"))
                         .Select(joinResult => joinResult.Danger);

            if (!string.IsNullOrEmpty(input.CheckOrganizationId))
            {
                var corg = await _context.Set<QHSESysOrganization>().FirstOrDefaultAsync(u => u.OrganizationId == input.CheckOrganizationId);
                if (corg != null)
                {
                    var escapedcNamePath = EscapeLikePattern(corg.NamePath);
                    query = query.Join(_context.Set<QHSESysOrganization>(),
                                       danger => danger.CheckOrganizationId,
                                       organization => organization.OrganizationId,
                                       (danger, organization) => new { Danger = danger, Organization = organization })
                                 .Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"%{escapedcNamePath}%", "/"))
                                 .Select(joinResult => joinResult.Danger);
                }
            }

            var dangerTypes = await _dictionaryService.GetDictionaryAsync("1010");
            var workSpecialitys = await _keyWorkSpecialtyService.GetAsync();

            List<Object> vio = new List<Object> { "各专业隐患性质" };
            vio.AddRange(workSpecialitys.Select(work => work.Specialty));
            result.Item.Add(vio);

            var dangerCounts = await query
                .GroupBy(d => new { d.DangerType, d.WorkSpecialty })
                .Select(g => new
                {
                    g.Key.DangerType,
                    g.Key.WorkSpecialty,
                    Count = g.Count()
                })
                .ToListAsync();

            foreach (var dangerType in dangerTypes)
            {
                List<Object> violation = new List<Object> { dangerType.Item };
                foreach (var workSpeciality in workSpecialitys)
                {
                    var value = dangerCounts.FirstOrDefault(v => v.DangerType == dangerType.Item && v.WorkSpecialty == workSpeciality.Specialty)?.Count ?? 0;
                    violation.Add(value);
                }
                result.Item.Add(violation);
            }
        }

        await _cacheService.SetDangerTypeWorkSpecialityAnalysisAsync(input, result);
        return result;
    }

    public async Task<CompareDangerAnalysisResult> DangerStatusAnalysisAsync(DangerAnalysisInput input)
    {
        var cachedResult = await _cacheService.GetDangerTypeDangerStatusAnalysisAsync(input);
        if (cachedResult != null)
        {
            return cachedResult;
        }

        CompareDangerAnalysisResult result = new CompareDangerAnalysisResult();
        var query = _context.Set<QHSESysDanger>().AsNoTracking().AsQueryable();

        if (!string.IsNullOrEmpty(input.DangerOrganizationName) && input.TimeOn != null && input.TimeEnd != null && input.CheckType.Count != 0)
        {
            var org = await _context.Set<QHSESysOrganization>().FirstOrDefaultAsync(u => u.OrganizationId == input.DangerOrganizationId);
            if (org == null) return result;

            var escapedNamePath = EscapeLikePattern(org.NamePath);
            query = query.Where(u => input.CheckType.Contains(u.CheckType))
                         .Where(u => u.CheckDate >= input.TimeOn && u.CheckDate <= input.TimeEnd)
                         .Join(_context.Set<QHSESysOrganization>(),
                               danger => danger.OrganizationId,
                               organization => organization.OrganizationId,
                               (danger, organization) => new { Danger = danger, Organization = organization })
                         .Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"{escapedNamePath}%", "/"))
                         .Select(joinResult => joinResult.Danger);

            if (!string.IsNullOrEmpty(input.CheckOrganizationId))
            {
                var corg = await _context.Set<QHSESysOrganization>().FirstOrDefaultAsync(u => u.OrganizationId == input.CheckOrganizationId);
                if (corg != null)
                {
                    var escapedcNamePath = EscapeLikePattern(corg.NamePath);
                    query = query.Join(_context.Set<QHSESysOrganization>(),
                                       danger => danger.CheckOrganizationId,
                                       organization => organization.OrganizationId,
                                       (danger, organization) => new { Danger = danger, Organization = organization })
                                 .Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"%{escapedcNamePath}%", "/"))
                                 .Select(joinResult => joinResult.Danger);
                }
            }

            var dangerTypes = await _dictionaryService.GetDictionaryAsync("1010");
            var dangerCounts = await query
                .GroupBy(d => new { d.DangerType, d.Status })
                .Select(g => new
                {
                    g.Key.DangerType,
                    g.Key.Status,
                    Count = g.Count()
                })
                .ToListAsync();

            List<Object> vio = new List<Object>
        {
            "隐患整改情况",
            "全部",
            "已整改",
            "未整改"
        };
            result.Item.Add(vio);

            foreach (var dangerType in dangerTypes)
            {
                List<Object> violation = new List<Object> { dangerType.Item };
                var total = dangerCounts.Where(dc => dc.DangerType == dangerType.Item).Sum(dc => dc.Count);
                var resolved = dangerCounts.FirstOrDefault(dc => dc.DangerType == dangerType.Item && dc.Status == "已整改")?.Count ?? 0;
                var unresolved = total - resolved;

                violation.Add(total);
                violation.Add(resolved);
                violation.Add(unresolved);
                result.Item.Add(violation);
            }
        }

        await _cacheService.SetDangerTypeDangerStatusAnalysisAsync(input, result);
        return result;
    }

    public async Task<CompareDangerAnalysisResult> DangerCategoryAnalysisAsync(DangerAnalysisInput input, string name)
    {
        var cachedResult = await _cacheService.GetDangerTypeDangerCategoryAnalysisAsync(input);
        if (cachedResult != null)
        {
            return cachedResult;
        }

        CompareDangerAnalysisResult result = new CompareDangerAnalysisResult();
        var query = _context.Set<QHSESysDanger>().AsNoTracking().AsQueryable();

        if (!string.IsNullOrEmpty(input.DangerOrganizationName) && input.TimeOn != null && input.TimeEnd != null && input.CheckType.Count != 0)
        {
            var org = await _context.Set<QHSESysOrganization>().FirstOrDefaultAsync(u => u.OrganizationId == input.DangerOrganizationId);
            if (org == null) return result;

            var escapedNamePath = EscapeLikePattern(org.NamePath);
            query = query.Where(u => input.CheckType.Contains(u.CheckType))
                         .Where(u => u.CheckDate >= input.TimeOn && u.CheckDate <= input.TimeEnd)
                         .Join(_context.Set<QHSESysOrganization>(),
                               danger => danger.OrganizationId,
                               organization => organization.OrganizationId,
                               (danger, organization) => new { Danger = danger, Organization = organization })
                         .Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"{escapedNamePath}%", "/"))
                         .Select(joinResult => joinResult.Danger);

            if (!string.IsNullOrEmpty(input.CheckOrganizationId))
            {
                var corg = await _context.Set<QHSESysOrganization>().FirstOrDefaultAsync(u => u.OrganizationId == input.CheckOrganizationId);
                if (corg != null)
                {
                    var escapedcNamePath = EscapeLikePattern(corg.NamePath);
                    query = query.Join(_context.Set<QHSESysOrganization>(),
                                       danger => danger.CheckOrganizationId,
                                       organization => organization.OrganizationId,
                                       (danger, organization) => new { Danger = danger, Organization = organization })
                                 .Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"%{escapedcNamePath}%", "/"))
                                 .Select(joinResult => joinResult.Danger);
                }
            }

            var dangerTypes = await _dictionaryService.GetDictionaryAsync("1010");
            var dangerCategorys = await _dangerCategoryService.GetSon(name);
            var dangerCategoryChildren = new Dictionary<string, List<string>>();

            foreach (var category in dangerCategorys)
            {
                var children = await _dangerCategoryService.GetChildren(category);
                dangerCategoryChildren[category] = children;
            }

            var dangerCounts = await query
                .GroupBy(d => new { d.DangerType, d.DangerCategory })
                .Select(g => new
                {
                    g.Key.DangerType,
                    g.Key.DangerCategory,
                    Count = g.Count()
                })
                .ToListAsync();

            var vio = new List<object> { name };
            vio.AddRange(dangerCategorys);
            result.Item.Add(vio);

            foreach (var dangerType in dangerTypes)
            {
                var violation = new List<object> { dangerType.Item };
                foreach (var category in dangerCategorys)
                {
                    var children = dangerCategoryChildren[category];
                    var count = dangerCounts.Where(dc => dc.DangerType == dangerType.Item && children.Contains(dc.DangerCategory)).Sum(dc => dc.Count);
                    violation.Add(count);
                }
                result.Item.Add(violation);
            }
        }

        await _cacheService.SetDangerTypeDangerCategoryAnalysisAsync(input, result);
        return result;
    }

    private async Task<List<int>> GetDangerCountsAsync(IQueryable<QHSESysDanger> query, string dangerType, List<string> dangerCategories)
    {
        var violationCounts = new List<int>();

        foreach (var dangerCategory in dangerCategories)
        {
            var children = await _dangerCategoryService.GetChildren(dangerCategory);
            var count = await query
                .Where(v => v.DangerType == dangerType && children.Contains(v.DangerCategory))
                .CountAsync();
            violationCounts.Add(count);
        }

        return violationCounts;
    }
}
